package com.pension.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import com.pension.model.dto.Member;

public class MemberDao {
	
	public void insertMember(Member member) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			//3. 명령 생성
			String sql = "INSERT INTO member "
					+ "(memberid, passwd, email, phone1, usertype) "
					//+ "VALUES (?, ?, ?, ?, ?);";
					+ "VALUES (?, password(?), ?, ?, ?);";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getMemberId());
			pstmt.setString(2, member.getPasswd());
			pstmt.setString(3, member.getEmail());
			pstmt.setString(4, member.getPhone1());
			pstmt.setString(5, member.getUserType());
			
			//4. 명령 실행
			pstmt.executeUpdate();
		} catch (Exception ex) {
			
		} finally {
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
	}
	
	public List<Member> getMembers() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Member> members = new ArrayList<Member>();
		
		try {
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
				"SELECT memberid, email, phone1, usertype, regdate " + 
				"FROM member " +
				"where deleted = false";
			pstmt = conn.prepareStatement(sql);						
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				Member member = new Member();
				member.setMemberId(rs.getString(1));
				member.setEmail(rs.getString(2));
				member.setPhone1(rs.getString(3));
				member.setUserType(rs.getString(4));
				member.setRegDate(rs.getDate(5));
				
				members.add(member);
			}
			
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return members;
	}
	
	public Member getMemberById(String id) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
				"SELECT memberid, email, phone1, usertype, regdate " + 
				"FROM member " +
				"WHERE memberid = ? and deleted = false";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			if (rs.next()) {
				member = new Member();
				member.setMemberId(rs.getString(1));
				member.setEmail(rs.getString(2));
				member.setPhone1(rs.getString(3));
				member.setUserType(rs.getString(4));
				member.setRegDate(rs.getDate(5));
			}
			
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return member;
	}

	public Member getMemberByIdAndPasswd(String id, String passwd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
				"SELECT memberid, email, phone1, usertype, regdate " + 
				"FROM member " +
				//"WHERE memberid = ? AND passwd = ?";
				"WHERE memberid = ? AND passwd = password(?) and deleted = false ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, passwd);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			if (rs.next()) {
				member = new Member();
				member.setMemberId(rs.getString(1));
				member.setEmail(rs.getString(2));
				member.setPhone1(rs.getString(3));
				member.setUserType(rs.getString(4));
				member.setRegDate(rs.getDate(5));
			}
			
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return member;
	}
	
	public void getDeleteMember(String id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
				"update member " + 
				"set deleted = true " +
				//"WHERE memberid = ? AND passwd = ?";
				"WHERE memberid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			//4. 명령 실행
			pstmt.executeUpdate();
			
			
		} catch (Exception ex) {
			
		} finally {
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
	}
	
	public void getModifyMember(String passwd, String email, String phone1, String memberId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
				"update member " + 
				"set passwd = password(?), email = ?, phone1 = ? " +
				//"WHERE memberid = ? AND passwd = ?";
				"WHERE memberid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, passwd);
			pstmt.setString(2, email);
			pstmt.setString(3, phone1);
			pstmt.setString(4, memberId);
			//4. 명령 실행
			pstmt.executeUpdate();
			
			
		} catch (Exception ex) {
			
		} finally {
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
	}

}























